package xupt.se.ttms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import xupt.se.ttms.idao.iMovieDAO;
import xupt.se.ttms.model.Movie;
import xupt.se.util.MyDBUtil;

public class MovieDAO implements iMovieDAO {
	private final String insertSql="insert into T_Movie(img,name,score,director,actor,type,time,date,introduction,price)values(?,?,?,?,?,?,?,?,?,?)";
	//通过主键id 删除
	private final String deleteSql="delete from T_Movie where mv_id=?";	
	
	private final String updateSql="update T_Movie set img=?,name=?,score=?,director=?,actor=?,type=?,time=?,date=?,introduction=?,price=? where mv_id=?";
	//通过主键id查询
	private final String selectSqlInt="select * from T_Movie where mv_id=?";
	//通过姓名name查询
	private final String selectSqlStr="select * from T_Movie where name=?";
	//查询全部
	private final String selectSqlAll="select * from T_Movie ";
	//最热影片
	private final String selectSqlHot="select * from T_Movie where score>?";
	//最新影片 
	private final String selectSqlNew="select * from T_Movie where date>?";
	//推荐影片
	private final String selectSqlRecommend="select * from T_Movie where score>?";
	

	/**
	 * 查询，传一个类型String,防sql注入攻击
	 * @param sql		sql语句
	 * @param str		where条件
	 * @return
	 * @throws Exception
	 */
	public ResultSet execQuery(String str) throws Exception {
		String sql=selectSqlStr;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, str);
		return rstSet;
	}
	/**
	 * 查询，传一个类型int,防sql注入攻击
	 * @param sql		sql语句
	 * @param i			where条件
	 * @return
	 * @throws Exception
	 */
	public ResultSet execQuery(int i) throws Exception {
		String sql=selectSqlInt;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, i);
		return rstSet;
	}	
	/**
	 * 删除 数据，传一个类型int，防sql注入攻击
	 * @param sql
	 * @param i
	 * @return
	 * @throws Exception
	 */
	public boolean execDelete(int i) throws Exception{
		String sql=deleteSql;
		boolean flag = false;
		MyDBUtil myDBUtil=new MyDBUtil();
		flag=myDBUtil.execDelete(sql, i);
		return flag;
	}
	@Override
	public ResultSet execQueryAll() throws Exception {
		String sql=selectSqlAll;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execAllQuery(sql);
		return rstSet;
	}
	@Override
	public List<Movie> HandleMovieResultSet(ResultSet rs) throws SQLException {
		 //结果，在第一个查询结果有个rs标记，通过rs.next()遍历获取全部
		Movie movie=null;
		List<Movie> movies=new ArrayList<>();
		 //获取结果的数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取该条结果有多少个列数据
        int columnCount = rsmd.getColumnCount();
        
       
        	System.out.println("获取电影数据中");
        	//通过rs.next()遍历获取全部结果
            while (rs.next()) {
            
            	movie=new Movie();
           	 //输出每条结果的全部数据
               for (int i = 0; i < columnCount; i++) {

            	   movie.setMv_id(rs.getInt("mv_id"));
            	   movie.setImg(rs.getString("img"));
            	   movie.setName(rs.getString("name"));
            	   movie.setScore(rs.getFloat("score"));
            	   movie.setDirector(rs.getString("director"));
            	   movie.setActor(rs.getString("actor"));
            	   movie.setType(rs.getString("type"));
            	
            	   movie.setTime(rs.getString("time"));
            	   movie.setDate(rs.getInt("date"));
            	   movie.setIntroduction(rs.getString("introduction"));
            	   movie.setPrice(rs.getFloat("price"));
            	   
                   System.out.print(rs.getString(i+1) + "\t");

               }
               movies.add(movie);
               System.out.println();		                    
            
        }
        return movies;
     
	}
	@Override
	public boolean execInsert(Movie movie) throws Exception {
		String sql=insertSql;
		Connection conn=null;
		boolean flag=false;
		PreparedStatement ps=null;
		MyDBUtil db = new MyDBUtil();
		conn=db.openConnection();
		try {
			if(conn==null)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
			ps.setString(1,movie.getImg());
			ps.setString(2,movie.getName());
			ps.setFloat(3,movie.getScore());
			ps.setString(4,movie.getDirector());
			ps.setString(5,movie.getActor());
			ps.setString(6,movie.getType());
			
			ps.setString(7,movie.getTime());
			ps.setInt(8,movie.getDate());
			ps.setString(9,movie.getIntroduction());
			ps.setFloat(10, movie.getPrice());
			flag=ps.execute();
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/			
			ps.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return flag;
	}
	@Override
	public boolean execUpdate(Movie movie) throws Exception {
		String sql=updateSql;
		Connection conn=null;
		boolean flag=false;
		PreparedStatement ps=null;
		MyDBUtil db = new MyDBUtil();
		conn=db.openConnection();
		try {
			if(conn==null)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
			ps.setString(1,movie.getImg());
			ps.setString(2,movie.getName());
			ps.setFloat(3,movie.getScore());
			ps.setString(4,movie.getDirector());
			ps.setString(5,movie.getActor());
			ps.setString(6,movie.getType());
			ps.setString(7,movie.getTime());
			ps.setInt(8,movie.getDate());
			ps.setString(9,movie.getIntroduction());
			ps.setFloat(10, movie.getPrice());
			
			ps.setInt(11, movie.getMv_id());
			
			flag=ps.execute();
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/			
			ps.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return flag;
	}

	@Override
	public ResultSet execQueryNew(int date) throws Exception {

		String sql=selectSqlNew;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, date);
		return rstSet;
	}
	@Override
	public ResultSet execQueryHot(float score) throws Exception {
		String sql=selectSqlHot;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql,score);
		return rstSet;
	}
	@Override
	public ResultSet execQueryRecommend(float score) throws Exception {
		String sql=selectSqlRecommend;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, score);
		return rstSet;
	}

	public ResultSet execQueryFuzzy(String name) throws Exception {
		String sql="select * from T_Movie where name like '%"+name+"%'";
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execAllQuery(sql);
		return rstSet;
	}	
}
